CREATE TRIGGER checkSupervisorContractConflict
ON Contract FOR INSERT, UPDATE AS
BEGIN
	DECLARE @supervisorId INT
	DECLARE @pharmacyName VARCHAR(100)
	DECLARE @startDate DATETIME
	DECLARE @endDate DATETIME
    DECLARE @updatedCount INT

    SET @updatedCount = (SELECT COUNT(*) FROM INSERTED)

    IF (@updatedCount = 1)
    BEGIN
	    SET @supervisorId = (SELECT supervisorId FROM INSERTED)
	    SET @pharmacyName = (SELECT pharmacyName FROM INSERTED)
	    SET @startDate = (SELECT startDate FROM INSERTED)
	    SET @endDate = (SELECT endDate FROM INSERTED)

	    IF EXISTS(
		    SELECT *
		    FROM Contract
		    WHERE supervisorId = @supervisorId
		    AND pharmacyName != @pharmacyName
		    AND (
			    (startDate <= @startDate AND @startDate <= endDate) /* startDate not within a contract */
			    OR
			    (startDate <= @endDate AND @endDate <= endDate) /* endDate not within a contract */
			    OR
			    (startDate >= @startDate AND @endDate >= endDate) /* startDate and endDate not around a contract */
		    )
		)
	    BEGIN
		    ROLLBACK
		    PRINT 'Supervisor cannot supervise contracts from two different Pharmacies in the same time period.'
	    END
    END
END
